<?php
require_once($CFG->ADODB_ROOT."/adodb.inc.php");
require_once($CFG->ADODB_ROOT."/adodb-exceptions.inc.php");

//
// db_init
//
function db_init () {
  global $CFG;
  global $DB;
  
  $DB = NewADOConnection($CFG->db_type);
  $DB->Connect($CFG->db_server,$CFG->db_user,$CFG->db_password,$CFG->db_database);
  
}


//
// get_records
//
function get_records ($table,$field1='',$value1='',$field2='',$value2='',$field3='',$value3='') {

 global $DB;
 $query="SELECT * FROM `$table` ";
 if ($field1 != '') {
  $query.="WHERE `$field1` = '".addslashes($value1)."' ";
  if ($field2 != '') {
   $query.="AND `$field2` = '".addslashes($value2)."' ";
  } 
  if ($field3 != '') {
   $query.="AND `$field3` = '".addslashes($value3)."' ";
  } 
 } 
 try {
  $result=$DB->GetAssoc($query);
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }
 return $result;
}

//
// get_record
//
function get_record ($table,$field1,$value1,$field2='',$value2='',$field3='',$value3='') {

 global $DB;

 $query="SELECT * FROM `$table` WHERE `$field1` = '".addslashes($value1)."' ";
 if ($field2 != '') {
  $query.="AND `$field2` = '".addslashes($value2)."' ";
 }
 if ($field3 != '') {
  $query.="AND `$field3` = '".addslashes($value3)."' ";
 }

 try {
  $result=$DB->GetRow($query);
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }
 
 return $result;

}


//
// insert_student
//
function insert_student($student) {
 global $DB;
 $ok=FALSE;
	if (!get_record("etudiant","noEtudiant",$student[noEtudiant])) {
	 $query = "INSERT INTO etudiant(noEtudiant,nom,prenom,dateNaissance) "
		        ."VALUES ('".addslashes($student[noEtudiant])."','".addslashes($student[nom])
										."','".addslashes($student[prenom])."','".addslashes($student[dateNaissance])."');";
		$ok=$DB->Execute($query);
	}
 return $ok;
}

//
// insert_enrolment
//
function insert_enrolment($enrolment) {
 global $DB;
 $ok=FALSE;
 $query="SELECT * FROM inscription WHERE noEtudiant = '".addslashes($enrolment[noEtudiant])."' "
       ."AND idObjetAPOGEE = '".addslashes($enrolment[idObjetAPOGEE])."' "
       ."AND annee = '".addslashes($enrolment[annee])."' ";
 try {
  $result=$DB->GetRow($query);
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }

	if (!$result) {
	 $query = "INSERT INTO inscription(noEtudiant,idObjetAPOGEE,annee,saisieAutorisee) "
		        ."VALUES ('".addslashes($enrolment[noEtudiant])."','".addslashes($enrolment[idObjetAPOGEE])
										."','".addslashes($enrolment[annee])."','".addslashes($enrolment[saisieAutorisee])."');";
		if($DB->Execute($query)) {
   $ok="insert";
  }
	} else {
  if ($result['saisieAutorisee'] and !$enrolment[saisieAutorisee]) {
 	 $query = "UPDATE inscription SET saisieAutorisee =  '".addslashes($enrolment[saisieAutorisee])."' "
 		        ."WHERE noEtudiant = '".addslashes($enrolment[noEtudiant])."' "
           ."AND idObjetAPOGEE = '".addslashes($enrolment[idObjetAPOGEE])."' "
 										."AND annee = '".addslashes($enrolment[annee])."';";
 		if($DB->Execute($query)) {
    $ok="update";
   }
  }
 }
 return $ok;
}


//
// insert_vdi_enrolment
//
function insert_vdi_enrolment($idDiplome,$studentID,$year) {
 global $DB;
 $ok=FALSE;
 
 $query="SELECT * FROM inscription WHERE noEtudiant = '".addslashes($studentID)."' "
       ."AND idObjetAPOGEE = '".addslashes($idDiplome)."' "
       ."AND annee = '".addslashes($year)."' ";
 try {
  $result=$DB->GetRow($query);
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }

	if (!$result) {
	 $query = "INSERT INTO inscription(noEtudiant,idObjetAPOGEE,annee) "
		        ."VALUES ('".addslashes($studentID)."','".addslashes($idDiplome)
										."','".addslashes($year)."');";
		if($DB->Execute($query)) {
   $ok="insert";
  }
	}
 return $ok;
}


//
// insert_vdi
//
function insert_vdi($vdi) {
 global $DB;
 $idDiplome=FALSE;
 
 $query="SELECT * FROM objetAPOGEE WHERE codeAPOGEE = '".addslashes($vdi[codeAPOGEE])."' "
       ."AND version = '".addslashes($vdi[version])."' "
       ."AND typeObjet = 'VDI' ";
 try {
  $result=$DB->GetRow($query);
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }
 
 $idDiplome=$result['idObjetAPOGEE'];

 $insert=0;
	if (!$result) {
	 $query = "INSERT INTO objetAPOGEE(codeAPOGEE,version,intitule,typeObjet) "
		        ."VALUES ('".addslashes($vdi[codeAPOGEE])."','".addslashes($vdi[version])
          ."','".addslashes($vdi[intitule])."','VDI');";
		if($DB->Execute($query)) {
   $insert=1;
   $idDiplome=$DB->Insert_ID();
  }
 }
 return array ($idDiplome,$insert);
}



//
// insert_vdi_object
//
function insert_vdi_object($idDiplome,$object) {
 global $DB;
 $ok=FALSE;
 
 $query="SELECT * FROM compositionDiplome "
       ."WHERE idObjetAPOGEEenfant = '".addslashes($object[idObjetAPOGEEenfant])."' "
       ."AND idDiplome = '".addslashes($idDiplome)."' ";
 try {
  $result=$DB->GetRow($query);
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }
 
	if (!$result) {
	 $query = "INSERT INTO compositionDiplome(idDiplome,idObjetAPOGEEenfant) "
		        ."VALUES ('".addslashes($idDiplome)."','".addslashes($object[idObjetAPOGEEenfant])."');";
		if($DB->Execute($query)) {
   $ok="insert";
  }
 }
 return $ok;
}

/*
//
// get_last_object_order
//
function get_last_object_order () {
 global $DB;
 try {
  $result=$DB->GetRow("SELECT ordre FROM objetAPOGEE ORDER BY ordre DESC LIMIT 0,1");
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }
 if (isset($result['ordre'])) {
  return $result['ordre'];
 } else {
  return 0;
 }
}
*/

//
// insert_object
//
function insert_object($object) {
 global $DB;
 $ok=FALSE;
	if (!get_idObjetAPOGEE($object[codeAPOGEE],$object[version])) {
  if ($object[version]=="") {
   $query = "INSERT INTO objetAPOGEE(codeAPOGEE,version,intitule,typeObjet) "
           ."VALUES ('".addslashes($object[codeAPOGEE])."',NULL,"
           ."'".addslashes($object[intitule])
           ."','".addslashes($object[typeObjet])."');";
  } else {
   $query = "INSERT INTO objetAPOGEE(codeAPOGEE,version,intitule,typeObjet) "
           ."VALUES ('".addslashes($object[codeAPOGEE])."','".addslashes($object[version])
           ."','".addslashes($object[intitule])
           ."','".addslashes($object[typeObjet])."');";
  }
		$ok=$DB->Execute($query);
	}
 return $ok;
}

//
// get_last_result
//
function get_last_result($noEtudiant,$idObjetAPOGEE,$annee) {
 global $DB;
 try {
  $result=$DB->GetRow("SELECT * FROM resultat WHERE noEtudiant = '".$noEtudiant."' "
                     ."AND idObjetAPOGEE = '".addslashes($idObjetAPOGEE)."' "
                     ."AND annee ='".$annee."' ORDER BY date DESC LIMIT 0,1;");
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }
 return $result;
}

//
// get_student_results
//
function get_student_results($noEtudiant,$sort="ordre",$hidden=FALSE) {
 global $DB;
 try {
  $query="SELECT r.*,o.intitule,o.codeAPOGEE, o.version, i.idObjetAPOGEE as idDiplome,cd.ordre "
        ."FROM resultat r, objetAPOGEE o, inscription i, compositionDiplome cd "
        ."WHERE r.noEtudiant = '".$noEtudiant."' "
        ."AND o.idObjetAPOGEE = r.idObjetAPOGEE "
        ."AND i.noEtudiant = '".$noEtudiant."' "
        ."AND i.annee = r.annee "
        ."AND cd.idObjetAPOGEEenfant = r.idObjetAPOGEE "
        ."AND cd.idDiplome = i.idObjetAPOGEE ";
  if (!$hidden) {
   $query .= "AND r.masquer = '0' ";
  }
  if ($sort == "date") {
   $query.= "ORDER BY r.date DESC";
  } else {
   $query.= "ORDER BY r.annee,i.idObjetAPOGEE,cd.ordre ASC, r.date DESC";
  }
  $result=$DB->GetAssoc($query);
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }
 return $result;
}

//
// get_cursus
//
function get_cursus ($idDiplome) {
 global $DB;
 try {
  $query="SELECT cd.*,oa.intitule,oa.codeAPOGEE,oa.version FROM compositionDiplome cd, objetAPOGEE oa "
        ."WHERE cd.idDiplome = '".$idDiplome."' "
        ."AND oa.idObjetAPOGEE = cd.idObjetAPOGEEenfant "
        ."ORDER BY ordre ";
  $result=$DB->GetAssoc($query);
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }
 $cursus=array();
 // Recherche des parents
 foreach ($result as $element) {
  if ($element['idObjetAPOGEEparent']=="") {
   $element['idObjetAPOGEEparent']=0;
  }
  $cursus[$element['idObjetAPOGEEparent']][]=array(
   'idObjetAPOGEE' => $element['idObjetAPOGEEenfant'],
   'codeAPOGEE'    => $element['codeAPOGEE'],
   'version'       => $element['version'],
   'intitule'      => $element['intitule']
   );   
 }
 return $cursus;
}


//
// get_vdi_enrolments
//
function get_vdi_enrolments($noEtudiant) {
 global $DB;
 try {
  $query="SELECT i.*,o.* FROM objetAPOGEE o, inscription i "
        ."WHERE i.noEtudiant = '".$noEtudiant."' "
        ."AND o.typeObjet = 'VDI' "
        ."AND o.idObjetAPOGEE = i.idObjetAPOGEE ORDER BY i.annee ASC";
  $result=$DB->GetAssoc($query);
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }
 return $result;
}


//
// insert_result
//
function insert_result($result) {
 global $DB;
 if (isset($result['session'])) {
  $query = "INSERT INTO resultat(noEtudiant,idObjetAPOGEE,source,valeur,resultat,session,annee,masquer) "
          ."VALUES ('".addslashes($result[noEtudiant])."','".addslashes($result[idObjetAPOGEE])
          ."','". addslashes($result[source])."','".addslashes($result[valeur])
          ."','". addslashes($result[resultat])."','".addslashes($result[session])
          ."','".addslashes($result[annee])."','".addslashes($result[masquer]) ."');";
 } else {
  $query = "INSERT INTO resultat(noEtudiant,idObjetAPOGEE,source,valeur,resultat,session,annee,masquer) "
          ."VALUES ('".addslashes($result[noEtudiant])."','".addslashes($result[idObjetAPOGEE])
          ."','". addslashes($result[source])."','".addslashes($result[valeur])
          ."','". addslashes($result[resultat])."',NULL, "
          ."'".addslashes($result[annee])."','".addslashes($result[masquer]) ."');";
 }
 $ok=$DB->Execute($query);
}

/*
//
// update_result
//
function update_result($result) {
 global $DB;
 $query = "UPDATE resultat SET valeur = '".addslashes($result[valeur])."', "
         ."resultat = '". addslashes($result[resultat])."', "
         ."source = '". addslashes($result[source])."', "
         ."date = NOW() "
         ."WHERE idResultat = '".$result[idResultat]."';";
 $ok=$DB->Execute($query);
}
*/

//
// insert_marks_and_results
//
function insert_marks_and_results($studentMarks,$results,$year,$source,$masquer) {
 global $DB;
 $insert=0;
 $update=0;
 foreach ($studentMarks as $noEtudiant => $marks) {
  foreach ($marks as $codeAPOGEE => $mark_version) {
   foreach ($mark_version as $version => $mark) {
    $resultat=$results[$noEtudiant][$codeAPOGEE][$version];
    if ($version==0) {
     $version="";
    }
    $idObjetAPOGEE=get_idObjetAPOGEE($codeAPOGEE,$version);
    $result=array( noEtudiant => $noEtudiant,
                   idObjetAPOGEE => $idObjetAPOGEE,
                   source     => $source,
                   valeur     => $mark['value'],
                   session    => $mark['session'],
                   resultat   => $resultat,
                   annee      => $year,
                   masquer    => $masquer);
    if (!$oldresult=get_last_result($noEtudiant,$idObjetAPOGEE,$year)) {
     insert_result($result);
     $insert++;
    } else {
     if (($oldresult[valeur] != $mark['value']) 
         or ($oldresult[resultat] != $resultat)
         or ($oldresult[masquer] != $masquer)
         or ($oldresult[session] != $mark['session'])
         or ($oldresult[source] != $source)) {
      insert_result($result);
      $update++;
     }
    }
   }
  }
 }
 return array ($insert, $update);
}

//
// get_pedagogical_inscriptions
//
function get_pedagogical_inscriptions ($idObjetAPOGEE) {
 global $DB,$CFG;
 
 try {
  $query="SELECT e.*,i.saisieAutorisee FROM etudiant e,inscription i "
        ."WHERE i.noEtudiant = e.noEtudiant "
        ."AND i.idObjetAPOGEE = '".addslashes($idObjetAPOGEE)."' AND i.annee = '{$CFG->current_year}' "
        ."ORDER BY e.nom, e.prenom ;";
  $result=$DB->GetAssoc($query);
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }
 return $result;
}

//
// insert_test
//
function insert_test($idObjetAPOGEE,$intitule,$coefficient=1) {
 global $DB,$CFG;
 $query = "INSERT INTO controle(idObjetAPOGEE,coefficient,intitule,annee) "
         ."VALUES ('".addslashes($idObjetAPOGEE)."','".addslashes($coefficient)
         ."','". addslashes($intitule)."','".addslashes($CFG->current_year)."');";
 $ok=$DB->Execute($query);
}

//
// insert_mark
//
function insert_mark($noEtudiant,$idControle,$note) {
 global $DB;
 $query = "INSERT INTO note(noEtudiant,idControle,source,valeur) "
         ."VALUES ('".addslashes($noEtudiant)."','".addslashes($idControle)
         ."','". addslashes($_SESSION['login'])."','".addslashes($note)."');";
 $ok=$DB->Execute($query);
}

//
// get_last_mark
//
function get_last_mark($noEtudiant,$idControle) {
 global $DB;
 
 $query="SELECT valeur FROM note "
        ."WHERE noEtudiant = '".addslashes($noEtudiant)."' "
        ."AND idControle = '".addslashes($idControle)."' "
        ."ORDER BY date DESC LIMIT 0,1 ;";
try {
  $result=$DB->GetRow($query);
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }
 return $result['valeur'];
}


//
// get_last_marks
//
function get_last_marks($noEtudiant) {
 global $DB,$CFG;
 
 try {
  $query="SELECT n.idNote,n.source,n.valeur,n.date,c.idControle, o.codeAPOGEE, c.coefficient, "
         ."c.intitule as intituleControle,o.intitule as intituleAPOGEE "
         ."FROM note n, controle c, objetAPOGEE o "
         ."WHERE n.idControle = c.idControle "
         ."AND n.noEtudiant = '".addslashes($noEtudiant)."' "
         ."AND o.idObjetAPOGEE = c.idObjetAPOGEE "
         ."ORDER BY n.date DESC "
         ."LIMIT 0, 10;";
  $result=$DB->GetAssoc($query);
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }
 return $result;
}

//
// get_student_marks
//
function get_student_marks($noEtudiant,$idObjetAPOGEE,$year) {
 global $DB,$CFG;
 
 try {
  $query="SELECT n.idNote,n.source,n.valeur,n.date, c.idControle, o.codeAPOGEE, c.coefficient, "
         ."c.intitule as intituleControle,o.intitule as intituleAPOGEE "
         ."FROM note n, controle c, objetAPOGEE o "
         ."WHERE n.idControle = c.idControle "
         ."AND n.noEtudiant = '".addslashes($noEtudiant)."' "
         ."AND o.idObjetAPOGEE = c.idObjetAPOGEE "
         ."AND c.idObjetAPOGEE = '".addslashes($idObjetAPOGEE)."' "
         ."AND c.annee = '".addslashes($year)."' "
         ."ORDER BY n.date DESC "
         ."LIMIT 0, 10;";
  $result=$DB->GetAssoc($query);
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }
 return $result;
}


//
// get_groups_with_ip
//
function get_groups_with_ip ($idObjetAPOGEE) {
 global $DB,$CFG;
 
 try {
  $query="SELECT DISTINCT g.idGroupe,g.intitule "
        ."FROM groupe g, etudiantDansGroupe edg, inscription i "
        ."WHERE g.idGroupe = edg.idGroupe "
        ."AND edg.noEtudiant = i.noEtudiant "
        ."AND i.idObjetAPOGEE = '".addslashes($idObjetAPOGEE)."' "
        ."AND i.annee = '{$CFG->current_year}' "
        ."AND edg.annee = '{$CFG->current_year}' "
        ."ORDER BY g.intitule ;";
  $result=$DB->GetAssoc($query);
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }
 return $result;
}


//
// has_saisieAutorisee
//
function has_saisieAutorisee ($noEtudiant,$idObjetAPOGEE,$year) {
 global $DB,$CFG;
 
 $query="SELECT idInscription, saisieAutorisee "
        ."FROM inscription "
        ."WHERE noEtudiant = '".addslashes($noEtudiant)."' "
        ."AND idObjetAPOGEE = '".addslashes($idObjetAPOGEE)."' "
        ."AND annee = '$year' ;";
 try {
  $result=$DB->GetRow($query);
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }
 return $result['saisieAutorisee'];
}

//
// get_idObjetAPOGEE
//
function get_idObjetAPOGEE ($codeAPOGEE,$version) {
 global $DB,$CFG;
 $return=FALSE;
 
 $query="SELECT idObjetAPOGEE "
        ."FROM objetAPOGEE "
        ."WHERE codeAPOGEE = '".addslashes($codeAPOGEE)."' ";
 if ($version == "") {
  $query.="AND version IS NULL ;";
 } else {
  $query.="AND version = '".addslashes($version)."' ;"; 
 }

 try {
  $result=$DB->GetRow($query);
 } catch (exception $e) {
  print_r($e);
  die("MYSQL ERROR");
 }
 if (isset($result['idObjetAPOGEE'])) {
  $return=$result['idObjetAPOGEE'];
 }
 return $return;
}
?>
